In [1]:
import pandas as pd
import numpy as np
import holoviews as hv
import hvplot.pandas
In [2]:
from bokeh.models.formatters import NumeralTickFormatter
formatter = NumeralTickFormatter(format="0,0")
In [3]:
from holoviews import opts
from holoviews.streams import Buffer
from bokeh.models import Range1d, LinearAxis, VBar

def plot_secondary(plot, element):
    """
    Hook to plot data on a secondary (twin) axis on a Holoviews Plot with Bokeh backend.
    More info:
    - http://holoviews.org/user_guide/Customizing_Plots.html#plot-hooks
    - https://docs.bokeh.org/en/latest/docs/user_guide/plotting.html#twin-axes
    
    """
    fig: Figure = plot.state
    glyph_first: GlyphRenderer = fig.renderers[0]  # will be the original plot
    glyph_last: GlyphRenderer = fig.renderers[-1] # will be the new plot
    right_axis_name = "twiny"
    # Create both axes if right axis does not exist
    if right_axis_name not in fig.extra_y_ranges.keys():
        # Recreate primary axis (left)
        if isinstance(glyph_first.glyph, VBar):
            y_first_name = glyph_first.glyph.top
        else:
            y_first_name = glyph_first.glyph.y
        
        #y_first_min = glyph_first.data_source.data[y_first_name].min()
        y_first_min = 0
        y_first_max = glyph_first.data_source.data[y_first_name].max()
        y_first_offset = (y_first_max - y_first_min) * 0.1
        fig.y_range = Range1d(
            start=y_first_min - y_first_offset,
            end=y_first_max + y_first_offset
        )
        fig.y_range.name = glyph_first.y_range_name
        # Create secondary axis (right)
        if isinstance(glyph_last.glyph, VBar):
            y_last_name = glyph_last.glyph.top
        else:
            y_last_name = glyph_last.glyph.y        
        #y_last_min = glyph_last.data_source.data[y_last_name].min()
        y_last_min = 0
        y_last_max = glyph_last.data_source.data[y_last_name].max()
        y_last_offset = (y_last_max - y_last_min) * 0.1
        fig.extra_y_ranges = {right_axis_name: Range1d(
            start=y_last_min - y_last_offset,
            end=y_last_max + y_last_offset
        )}
        fig.add_layout(LinearAxis(y_range_name=right_axis_name, axis_label=y_last_name), "right")
    # Set right axis for the last glyph added to the figure
    glyph_last.y_range_name = right_axis_name
    
    
def plot_secondary_3_axes(plot, element):
    """
    Hook to plot data on a secondary (twin) axis on a Holoviews Plot with Bokeh backend.
    More info:
    - http://holoviews.org/user_guide/Customizing_Plots.html#plot-hooks
    - https://docs.bokeh.org/en/latest/docs/user_guide/plotting.html#twin-axes
    
    """
    fig: Figure = plot.state
    
    glyph_first: GlyphRenderer = fig.renderers[0]  # will be the original plot
    glyph_middle: GlyphRenderer = fig.renderers[1]  # will be the middle plot
    glyph_last: GlyphRenderer = fig.renderers[2] # will be the new plot
    right_axis_name_middle = "twiny_middle"
    right_axis_name_last = "twiny_last"
    # Create both axes if right axis does not exist
    if right_axis_name_last not in fig.extra_y_ranges.keys():
        # Recreate primary axis (left)
        if isinstance(glyph_first.glyph, VBar):
            y_first_name = glyph_first.glyph.top
        else:
            y_first_name = glyph_first.glyph.y
        
        #y_first_min = glyph_first.data_source.data[y_first_name].min()
        y_first_min = 0
        y_first_max = glyph_first.data_source.data[y_first_name].max()
        y_first_offset = (y_first_max - y_first_min) * 0.1
        fig.y_range = Range1d(
            start=y_first_min - y_first_offset,
            end=y_first_max + y_first_offset
        )
        fig.y_range.name = glyph_first.y_range_name
    
        # Create secondary axis (right)
        if isinstance(glyph_middle.glyph, VBar):
            y_middle_name = glyph_middle.glyph.top
        else:
            y_middle_name = glyph_middle.glyph.y    
        
        #y_middle_min = glyph_middle.data_source.data[y_last_name].min()
        y_middle_min = 0
        y_middle_max = glyph_middle.data_source.data[y_middle_name].max()
        y_middle_offset = (y_middle_max - y_middle_min) * 0.1
        #fig.extra_y_ranges = {right_axis_name_middle: Range1d(
        #    start=y_middle_min - y_middle_offset,
        #    end=y_middle_max + y_middle_offset
        #)}
        #fig.add_layout(LinearAxis(y_range_name=right_axis_name_middle, axis_label=y_middle_name), "right")
        
         # Create secondary axis (right)
        if isinstance(glyph_last.glyph, VBar):
            y_last_name = glyph_last.glyph.top
        else:
            y_last_name = glyph_last.glyph.y        
        
        #y_last_min = glyph_last.data_source.data[y_last_name].min()
        y_last_min = 0
        y_last_max = glyph_last.data_source.data[y_last_name].max()
        y_last_offset = (y_last_max - y_last_min) * 0.1
        fig.extra_y_ranges = {right_axis_name_last: Range1d(
            start=y_last_min - y_last_offset,
            end=y_last_max + y_last_offset
        ), 
        right_axis_name_middle: Range1d(
            start=y_middle_min - y_middle_offset,
            end=y_middle_max + y_middle_offset
        )}
        fig.add_layout(LinearAxis(y_range_name=right_axis_name_middle, axis_label=y_middle_name), "right")
        fig.add_layout(LinearAxis(y_range_name=right_axis_name_last, axis_label=y_last_name), "right")
        
    # Set right axis for the last glyph added to the figure
    glyph_middle.y_range_name = right_axis_name_middle
    glyph_last.y_range_name = right_axis_name_last
In [4]:
hv.extension('bokeh')
In [5]:
scotman_df = pd.read_csv('data/ScottishManufRevInScotland.csv')
cols = scotman_df.columns.drop('Description')
scotman_df[cols] = scotman_df[cols].apply(pd.to_numeric, errors='coerce').fillna(0)
scotman_df.index = scotman_df['Description']
scotman_df.drop(['Year', 'Division/\nSection', 'Description'], axis=1, inplace=True)
cols = scotman_df.columns
cols_fixed = [col.replace('\n', ' ').replace('�','£') for col in cols]
cols_fixed
scotman_df.columns = cols_fixed
scotman_df
Out[5]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+
Description
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0
In [6]:
#scotman_df.loc['Manufacture of Coke and Refined Petroleum', 'Number: 500+ employees'] = 5  ## Given dataset has 0, but Bus In Scot data shows 5 in 250+ category
In [7]:
scotman_df_1 = scotman_df.copy()
scotman_df_1['total number'] = scotman_df_1.sum(axis=1)
scotman_df_1 = scotman_df_1.sort_values(by='total number', ascending = False)
scotman_df_1 = scotman_df_1.drop ('total number', axis=1)
scotman_df_1.hvplot.bar(height=2000, width=2000, stacked=True, rot=75, yformatter=formatter, ylabel='Count of companies').opts(fontscale=1.5)
Out[7]:
In [8]:
scotman_totals_df = pd.read_csv('data/ScottishManufInScotlandTotals_clean2.csv')
cols = scotman_totals_df.columns.drop('Description')
scotman_totals_df[cols] = scotman_totals_df[cols].apply(pd.to_numeric, errors='coerce').fillna(0)
scotman_totals_df.index = scotman_totals_df['Description']
cols = scotman_totals_df.columns
cols_fixed = [col.replace('\n', ' ').replace('�','£') for col in cols]
scotman_totals_df.columns = cols_fixed
scotman_totals_df['Total Scottish turnover (£)'] = 1000000 * scotman_totals_df['Total Scottish turnover (£m)']
scotman_totals_df.drop(['Description', 'Total Scottish employment', 'Total Scottish turnover (£m)'], axis=1, inplace=True)
scotman_totals_df
Out[8]:
Total Scottish turnover (£)
Description
Manufacture of Food Products 5.430000e+09
Manufacture of Beverages 6.636000e+09
Manufacture of Tobacco Products 0.000000e+00
Manufacture of Textiles 4.770000e+08
Manufacture of Wearing Apparel 0.000000e+00
Manufacture of Leather and Related Products 0.000000e+00
Manufacture of Wood and of Products of Wood and Cork 1.591000e+09
Manufacture of Paper and Paper Products 8.630000e+08
Printing and Reproduction of Recorded Media 3.520000e+08
Manufacture of Coke and Refined Petroleum 4.483000e+09
Manufacture of Chemicals and Chemical Products 2.105000e+09
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 2.089000e+09
Manufacture of Rubber and Plastic Products 1.190000e+09
Manufacture of Other Non-Metallic Mineral Products 9.400000e+08
Manufacture of Basic Metals 5.830000e+08
Manufacture of Fabricated and Metal Products 2.692000e+09
Manufacture of Computer, Electronic and Optical Products 2.118000e+09
Manufacture of Electrical Equipment 8.040000e+08
Manufacture of Machinery and Equipment Not Elsewhere Classified 2.580000e+09
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 7.260000e+08
Manufacture of Other Transport Equipment 2.499000e+09
Manufacture of Furniture 2.220000e+08
Other Manufacturing 5.160000e+08
Repair and Installation of Machinery and Equipment 3.495000e+09
In [9]:
cols = scotman_df.columns
centres = [25000, 75000, 150000, 600000, 3000000, 0]
centres_dict = dict(zip(cols, centres))
centres_dict
Out[9]:
{'Number: £0- £50,000': 25000,
 'Number: £50,001-£100,000': 75000,
 'Number: £100,001-£200,000': 150000,
 'Number: £200,001-£1,000,000': 600000,
 'Number: £1,000,001-£5,000,000': 3000000,
 'Number: £5,000,001+': 0}
In [10]:
def estimate_total_revs(row):
    total = sum([row[col] * centres_dict[col] for col in row.index])
    return total

def estimate_revs(row):
    for col in row.index:
        new_col = col.replace('Number', 'Estimated Revenues')
        row[new_col] = row[col] * centres_dict[col]
    return row
In [12]:
scotman_df_3 = scotman_df.copy()
scotman_df_3_totals = scotman_df_3.apply(estimate_revs, axis=1)
scotman_df_3_totals
Out[12]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+ Estimated Revenues: £0- £50,000 Estimated Revenues: £50,001-£100,000 Estimated Revenues: £100,001-£200,000 Estimated Revenues: £200,001-£1,000,000 Estimated Revenues: £1,000,001-£5,000,000 Estimated Revenues: £5,000,001+
Description
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0 3000000.0 7125000.0 21000000.0 117000000.0 375000000.0 0.0
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0 2750000.0 4500000.0 7500000.0 48000000.0 90000000.0 0.0
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0 1250000.0 4125000.0 9750000.0 48000000.0 90000000.0 0.0
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0 1125000.0 3750000.0 5250000.0 27000000.0 45000000.0 0.0
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0 250000.0 375000.0 0.0 3000000.0 15000000.0 0.0
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0 2125000.0 25125000.0 52500000.0 120000000.0 240000000.0 0.0
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0 125000.0 750000.0 750000.0 12000000.0 45000000.0 0.0
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0 2375000.0 7500000.0 17250000.0 72000000.0 135000000.0 0.0
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 0.0 0.0 0.0 0.0
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0 1000000.0 1875000.0 5250000.0 27000000.0 105000000.0 0.0
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0 125000.0 375000.0 750000.0 3000000.0 15000000.0 0.0
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0 500000.0 2250000.0 2250000.0 48000000.0 180000000.0 0.0
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0 500000.0 3000000.0 5250000.0 42000000.0 120000000.0 0.0
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0 250000.0 1500000.0 2250000.0 12000000.0 60000000.0 0.0
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0 4500000.0 53625000.0 52500000.0 240000000.0 720000000.0 0.0
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0 1375000.0 4125000.0 6750000.0 51000000.0 180000000.0 0.0
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0 375000.0 1500000.0 2250000.0 24000000.0 105000000.0 0.0
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0 1375000.0 5625000.0 11250000.0 84000000.0 240000000.0 0.0
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0 500000.0 3000000.0 4500000.0 15000000.0 60000000.0 0.0
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0 625000.0 4875000.0 6000000.0 18000000.0 45000000.0 0.0
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0 750000.0 3375000.0 11250000.0 51000000.0 75000000.0 0.0
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0 2625000.0 10875000.0 19500000.0 114000000.0 120000000.0 0.0
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0 4375000.0 33750000.0 36750000.0 144000000.0 300000000.0 0.0
In [13]:
scotman_df_3['estimated total revenues'] = scotman_df_3.apply(estimate_total_revs, axis=1)
scotman_df_3
Out[13]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+ estimated total revenues
Description
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0 5.231250e+08
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0 1.527500e+08
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0 0.000000e+00
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0 1.531250e+08
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0 8.212500e+07
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0 1.862500e+07
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0 4.397500e+08
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0 5.862500e+07
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0 2.341250e+08
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0 0.000000e+00
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0 1.401250e+08
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0 1.925000e+07
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0 2.330000e+08
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0 1.707500e+08
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0 7.600000e+07
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0 1.070625e+09
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0 2.432500e+08
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0 1.331250e+08
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0 3.422500e+08
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0 8.300000e+07
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0 7.450000e+07
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0 1.413750e+08
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0 2.670000e+08
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0 5.188750e+08
In [14]:
scotman_df_3_totals['Estimated Revenues: £5,000,001+'] = scotman_totals_df['Total Scottish turnover (£)'] - scotman_df_3['estimated total revenues']
scotman_df_3_totals['Total Revenues'] = scotman_totals_df['Total Scottish turnover (£)']
scotman_df_3_totals
Out[14]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+ Estimated Revenues: £0- £50,000 Estimated Revenues: £50,001-£100,000 Estimated Revenues: £100,001-£200,000 Estimated Revenues: £200,001-£1,000,000 Estimated Revenues: £1,000,001-£5,000,000 Estimated Revenues: £5,000,001+ Total Revenues
Description
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0 3000000.0 7125000.0 21000000.0 117000000.0 375000000.0 4.906875e+09 5.430000e+09
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0 2750000.0 4500000.0 7500000.0 48000000.0 90000000.0 6.483250e+09 6.636000e+09
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.000000e+00 0.000000e+00
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0 1250000.0 4125000.0 9750000.0 48000000.0 90000000.0 3.238750e+08 4.770000e+08
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0 1125000.0 3750000.0 5250000.0 27000000.0 45000000.0 -8.212500e+07 0.000000e+00
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0 250000.0 375000.0 0.0 3000000.0 15000000.0 -1.862500e+07 0.000000e+00
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0 2125000.0 25125000.0 52500000.0 120000000.0 240000000.0 1.151250e+09 1.591000e+09
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0 125000.0 750000.0 750000.0 12000000.0 45000000.0 8.043750e+08 8.630000e+08
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0 2375000.0 7500000.0 17250000.0 72000000.0 135000000.0 1.178750e+08 3.520000e+08
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 0.0 0.0 0.0 4.483000e+09 4.483000e+09
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0 1000000.0 1875000.0 5250000.0 27000000.0 105000000.0 1.964875e+09 2.105000e+09
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0 125000.0 375000.0 750000.0 3000000.0 15000000.0 2.069750e+09 2.089000e+09
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0 500000.0 2250000.0 2250000.0 48000000.0 180000000.0 9.570000e+08 1.190000e+09
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0 500000.0 3000000.0 5250000.0 42000000.0 120000000.0 7.692500e+08 9.400000e+08
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0 250000.0 1500000.0 2250000.0 12000000.0 60000000.0 5.070000e+08 5.830000e+08
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0 4500000.0 53625000.0 52500000.0 240000000.0 720000000.0 1.621375e+09 2.692000e+09
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0 1375000.0 4125000.0 6750000.0 51000000.0 180000000.0 1.874750e+09 2.118000e+09
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0 375000.0 1500000.0 2250000.0 24000000.0 105000000.0 6.708750e+08 8.040000e+08
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0 1375000.0 5625000.0 11250000.0 84000000.0 240000000.0 2.237750e+09 2.580000e+09
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0 500000.0 3000000.0 4500000.0 15000000.0 60000000.0 6.430000e+08 7.260000e+08
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0 625000.0 4875000.0 6000000.0 18000000.0 45000000.0 2.424500e+09 2.499000e+09
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0 750000.0 3375000.0 11250000.0 51000000.0 75000000.0 8.062500e+07 2.220000e+08
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0 2625000.0 10875000.0 19500000.0 114000000.0 120000000.0 2.490000e+08 5.160000e+08
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0 4375000.0 33750000.0 36750000.0 144000000.0 300000000.0 2.976125e+09 3.495000e+09
In [15]:
scotman_df_3_totals = scotman_df_3_totals.sort_values(by='Total Revenues', ascending = False)
In [16]:
scotman_df_3_totals[['Total Revenues']].hvplot.bar(height=2000, width=2000, stacked=True, rot=75, yformatter=formatter).opts(fontscale=1.5)
Out[16]:
In [17]:
scotman_df_3_totals['cumulative_total'] = scotman_df_3_totals[['Total Revenues']].cumsum()
scotman_df_3_totals['cumulative_perc'] = 100 * scotman_df_3_totals['cumulative_total'] / scotman_df_3_totals['Total Revenues'].sum()

scotman_df_3_totals
Out[17]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+ Estimated Revenues: £0- £50,000 Estimated Revenues: £50,001-£100,000 Estimated Revenues: £100,001-£200,000 Estimated Revenues: £200,001-£1,000,000 Estimated Revenues: £1,000,001-£5,000,000 Estimated Revenues: £5,000,001+ Total Revenues cumulative_total cumulative_perc
Description
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0 2750000.0 4500000.0 7500000.0 48000000.0 90000000.0 6.483250e+09 6.636000e+09 6.636000e+09 15.654266
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0 3000000.0 7125000.0 21000000.0 117000000.0 375000000.0 4.906875e+09 5.430000e+09 1.206600e+10 28.463589
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 0.0 0.0 0.0 4.483000e+09 4.483000e+09 1.654900e+10 39.038947
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0 4375000.0 33750000.0 36750000.0 144000000.0 300000000.0 2.976125e+09 3.495000e+09 2.004400e+10 47.283622
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0 4500000.0 53625000.0 52500000.0 240000000.0 720000000.0 1.621375e+09 2.692000e+09 2.273600e+10 53.634026
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0 1375000.0 5625000.0 11250000.0 84000000.0 240000000.0 2.237750e+09 2.580000e+09 2.531600e+10 59.720224
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0 625000.0 4875000.0 6000000.0 18000000.0 45000000.0 2.424500e+09 2.499000e+09 2.781500e+10 65.615343
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0 1375000.0 4125000.0 6750000.0 51000000.0 180000000.0 1.874750e+09 2.118000e+09 2.993300e+10 70.611686
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0 1000000.0 1875000.0 5250000.0 27000000.0 105000000.0 1.964875e+09 2.105000e+09 3.203800e+10 75.577363
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0 125000.0 375000.0 750000.0 3000000.0 15000000.0 2.069750e+09 2.089000e+09 3.412700e+10 80.505296
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0 2125000.0 25125000.0 52500000.0 120000000.0 240000000.0 1.151250e+09 1.591000e+09 3.571800e+10 84.258451
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0 500000.0 2250000.0 2250000.0 48000000.0 180000000.0 9.570000e+08 1.190000e+09 3.690800e+10 87.065651
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0 500000.0 3000000.0 5250000.0 42000000.0 120000000.0 7.692500e+08 9.400000e+08 3.784800e+10 89.283103
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0 125000.0 750000.0 750000.0 12000000.0 45000000.0 8.043750e+08 8.630000e+08 3.871100e+10 91.318912
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0 375000.0 1500000.0 2250000.0 24000000.0 105000000.0 6.708750e+08 8.040000e+08 3.951500e+10 93.215541
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0 500000.0 3000000.0 4500000.0 15000000.0 60000000.0 6.430000e+08 7.260000e+08 4.024100e+10 94.928169
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0 250000.0 1500000.0 2250000.0 12000000.0 60000000.0 5.070000e+08 5.830000e+08 4.082400e+10 96.303461
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0 2625000.0 10875000.0 19500000.0 114000000.0 120000000.0 2.490000e+08 5.160000e+08 4.134000e+10 97.520700
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0 1250000.0 4125000.0 9750000.0 48000000.0 90000000.0 3.238750e+08 4.770000e+08 4.181700e+10 98.645939
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0 2375000.0 7500000.0 17250000.0 72000000.0 135000000.0 1.178750e+08 3.520000e+08 4.216900e+10 99.476304
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0 750000.0 3375000.0 11250000.0 51000000.0 75000000.0 8.062500e+07 2.220000e+08 4.239100e+10 100.000000
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0 250000.0 375000.0 0.0 3000000.0 15000000.0 -1.862500e+07 0.000000e+00 4.239100e+10 100.000000
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.000000e+00 0.000000e+00 4.239100e+10 100.000000
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0 1125000.0 3750000.0 5250000.0 27000000.0 45000000.0 -8.212500e+07 0.000000e+00 4.239100e+10 100.000000
In [18]:
chart1 = scotman_df_3_totals[['Total Revenues']].hvplot.bar(height=2000, width=2000, rot=75, yformatter=formatter).options(fontscale=1.5, framewise=True, yaxis='left', show_grid=True)
chart2 = scotman_df_3_totals[['cumulative_perc']].hvplot.line(height=2000, width=2000, rot=75, ylim=(0,100)).options(fontscale=1.5, framewise=True,hooks=[plot_secondary], color='red', show_grid=True)

(chart1 * chart2)
Out[18]:
In [19]:
use_cols = [col for col in scotman_df_3_totals.columns if 'Estimated Revenues' in col]
use_cols
Out[19]:
['Estimated Revenues: £0- £50,000',
 'Estimated Revenues: £50,001-£100,000',
 'Estimated Revenues: £100,001-£200,000',
 'Estimated Revenues: £200,001-£1,000,000',
 'Estimated Revenues: £1,000,001-£5,000,000',
 'Estimated Revenues: £5,000,001+']
In [20]:
scotman_df_4 = scotman_df_3_totals[use_cols]
In [21]:
scotman_df_4.hvplot.bar(height=2000, width=2000, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.5)

chart1 = scotman_df_4.hvplot.bar(height=2000, width=2000, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.5).options(fontscale=1.5, framewise=True, yaxis='left', show_grid=True)
chart2 = scotman_df_3_totals[['cumulative_perc']].hvplot.line(height=2000, width=2000, rot=75, ylim=(0,100)).options(fontscale=1.5, framewise=True,hooks=[plot_secondary], color='red', show_grid=True)

(chart1 * chart2)
Out[21]:
In [22]:
scotman_df_3_totals.loc["Total"] = scotman_df_3_totals.sum()
scotman_df_3_totals
Out[22]:
Number: £0- £50,000 Number: £50,001-£100,000 Number: £100,001-£200,000 Number: £200,001-£1,000,000 Number: £1,000,001-£5,000,000 Number: £5,000,001+ Estimated Revenues: £0- £50,000 Estimated Revenues: £50,001-£100,000 Estimated Revenues: £100,001-£200,000 Estimated Revenues: £200,001-£1,000,000 Estimated Revenues: £1,000,001-£5,000,000 Estimated Revenues: £5,000,001+ Total Revenues cumulative_total cumulative_perc
Description
Manufacture of Beverages 110.0 60.0 50.0 80.0 30.0 45.0 2750000.0 4500000.0 7500000.0 4.800000e+07 9.000000e+07 6.483250e+09 6.636000e+09 6.636000e+09 15.654266
Manufacture of Food Products 120.0 95.0 140.0 195.0 125.0 130.0 3000000.0 7125000.0 21000000.0 1.170000e+08 3.750000e+08 4.906875e+09 5.430000e+09 1.206600e+10 28.463589
Manufacture of Coke and Refined Petroleum 0.0 0.0 0.0 0.0 0.0 10.0 0.0 0.0 0.0 0.000000e+00 0.000000e+00 4.483000e+09 4.483000e+09 1.654900e+10 39.038947
Repair and Installation of Machinery and Equipment 175.0 450.0 245.0 240.0 100.0 60.0 4375000.0 33750000.0 36750000.0 1.440000e+08 3.000000e+08 2.976125e+09 3.495000e+09 2.004400e+10 47.283622
Manufacture of Fabricated and Metal Products 180.0 715.0 350.0 400.0 240.0 95.0 4500000.0 53625000.0 52500000.0 2.400000e+08 7.200000e+08 1.621375e+09 2.692000e+09 2.273600e+10 53.634026
Manufacture of Machinery and Equipment Not Elsewhere Classified 55.0 75.0 75.0 140.0 80.0 60.0 1375000.0 5625000.0 11250000.0 8.400000e+07 2.400000e+08 2.237750e+09 2.580000e+09 2.531600e+10 59.720224
Manufacture of Other Transport Equipment 25.0 65.0 40.0 30.0 15.0 15.0 625000.0 4875000.0 6000000.0 1.800000e+07 4.500000e+07 2.424500e+09 2.499000e+09 2.781500e+10 65.615343
Manufacture of Computer, Electronic and Optical Products 55.0 55.0 45.0 85.0 60.0 45.0 1375000.0 4125000.0 6750000.0 5.100000e+07 1.800000e+08 1.874750e+09 2.118000e+09 2.993300e+10 70.611686
Manufacture of Chemicals and Chemical Products 40.0 25.0 35.0 45.0 35.0 40.0 1000000.0 1875000.0 5250000.0 2.700000e+07 1.050000e+08 1.964875e+09 2.105000e+09 3.203800e+10 75.577363
Manufacture of Basic Pharmaceutical Products and Pharmaceutical Preparations 5.0 5.0 5.0 5.0 5.0 15.0 125000.0 375000.0 750000.0 3.000000e+06 1.500000e+07 2.069750e+09 2.089000e+09 3.412700e+10 80.505296
Manufacture of Wood and of Products of Wood and Cork 85.0 335.0 350.0 200.0 80.0 35.0 2125000.0 25125000.0 52500000.0 1.200000e+08 2.400000e+08 1.151250e+09 1.591000e+09 3.571800e+10 84.258451
Manufacture of Rubber and Plastic Products 20.0 30.0 15.0 80.0 60.0 35.0 500000.0 2250000.0 2250000.0 4.800000e+07 1.800000e+08 9.570000e+08 1.190000e+09 3.690800e+10 87.065651
Manufacture of Other Non-Metallic Mineral Products 20.0 40.0 35.0 70.0 40.0 30.0 500000.0 3000000.0 5250000.0 4.200000e+07 1.200000e+08 7.692500e+08 9.400000e+08 3.784800e+10 89.283103
Manufacture of Paper and Paper Products 5.0 10.0 5.0 20.0 15.0 25.0 125000.0 750000.0 750000.0 1.200000e+07 4.500000e+07 8.043750e+08 8.630000e+08 3.871100e+10 91.318912
Manufacture of Electrical Equipment 15.0 20.0 15.0 40.0 35.0 20.0 375000.0 1500000.0 2250000.0 2.400000e+07 1.050000e+08 6.708750e+08 8.040000e+08 3.951500e+10 93.215541
Manufacture of Motor Vehicles, Trailers and Semi-Trailers 20.0 40.0 30.0 25.0 20.0 10.0 500000.0 3000000.0 4500000.0 1.500000e+07 6.000000e+07 6.430000e+08 7.260000e+08 4.024100e+10 94.928169
Manufacture of Basic Metals 10.0 20.0 15.0 20.0 20.0 20.0 250000.0 1500000.0 2250000.0 1.200000e+07 6.000000e+07 5.070000e+08 5.830000e+08 4.082400e+10 96.303461
Other Manufacturing 105.0 145.0 130.0 190.0 40.0 15.0 2625000.0 10875000.0 19500000.0 1.140000e+08 1.200000e+08 2.490000e+08 5.160000e+08 4.134000e+10 97.520700
Manufacture of Textiles 50.0 55.0 65.0 80.0 30.0 20.0 1250000.0 4125000.0 9750000.0 4.800000e+07 9.000000e+07 3.238750e+08 4.770000e+08 4.181700e+10 98.645939
Printing and Reproduction of Recorded Media 95.0 100.0 115.0 120.0 45.0 15.0 2375000.0 7500000.0 17250000.0 7.200000e+07 1.350000e+08 1.178750e+08 3.520000e+08 4.216900e+10 99.476304
Manufacture of Furniture 30.0 45.0 75.0 85.0 25.0 5.0 750000.0 3375000.0 11250000.0 5.100000e+07 7.500000e+07 8.062500e+07 2.220000e+08 4.239100e+10 100.000000
Manufacture of Leather and Related Products 10.0 5.0 0.0 5.0 5.0 0.0 250000.0 375000.0 0.0 3.000000e+06 1.500000e+07 -1.862500e+07 0.000000e+00 4.239100e+10 100.000000
Manufacture of Tobacco Products 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.000000e+00 0.000000e+00 0.000000e+00 0.000000e+00 4.239100e+10 100.000000
Manufacture of Wearing Apparel 45.0 50.0 35.0 45.0 15.0 5.0 1125000.0 3750000.0 5250000.0 2.700000e+07 4.500000e+07 -8.212500e+07 0.000000e+00 4.239100e+10 100.000000
Total 1275.0 2440.0 1870.0 2200.0 1120.0 750.0 31875000.0 183000000.0 280500000.0 1.320000e+09 3.360000e+09 3.721562e+10 4.239100e+10 7.919150e+11 1868.120592
In [28]:
def create_cum_df(sip):
    est_emp_cols = [col for col in scotman_df_3_totals.columns if 'Estimated Revenues' in col]
    num_cols = [col for col in scotman_df_3_totals.columns if 'Number' in col]
    use_rows = [col.replace('Estimated Revenues: ','') for col in use_cols ]
    
    sip_df = pd.DataFrame(scotman_df_3_totals.loc[sip])
    emp_df = sip_df.loc[est_emp_cols]
    emp_df.rename(columns={sip: 'Total Revenues'}, inplace=True)
    emp_df.index = use_rows
    num_df = sip_df.loc[num_cols]
    num_df.rename(columns={sip: 'Number of Companies'}, inplace=True)
    num_df.index = use_rows
    df = emp_df.join(num_df)
    df = df.iloc[::-1]
    df['cumulative_sum'] = df['Total Revenues'].cumsum()
    s = df['Total Revenues'].sum()
    if s > 0:
        df['cumulative_perc'] = 100 * df['cumulative_sum'] / s
    else:
        df['cumulative_perc'] = 100
    return df
In [29]:
sip = scotman_df_3_totals.index[1]

def create_cum_charts(sip):
    df = create_cum_df(sip)
    chart1 = df[['Total Revenues']].hvplot.bar(height=1000, width=1000, rot=75, yformatter=formatter, title=sip).options(fontscale=1.5, framewise=True, yaxis='left', show_grid=True)
    chart2 = df[['Number of Companies']].hvplot.bar(height=1000, width=1000, bar_width=0.1, rot=75, ylim=(0,100), ).options(fontscale=1.5, framewise=True,hooks=[plot_secondary_3_axes], color='green', show_grid=True)
    chart3 = df[['cumulative_perc']].hvplot.line(height=1000, width=1000, rot=75, ylim=(0,100)).options(fontscale=1.5, framewise=True,hooks=[plot_secondary_3_axes], color='red', show_grid=True)

    return chart1 * chart2 * chart3
In [31]:
charts = None
for sip in scotman_df_3_totals.index:
    cs = create_cum_charts(sip)
    if charts == None:
        charts = cs
    else:
        charts = charts + cs
    #print(sip, ':', cs, ':', charts)
layout = hv.Layout(charts).cols(1)
layout
WARNING:param.BarPlot36696: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot36853: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37009: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37165: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37321: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37477: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37633: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37789: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot37945: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38101: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38257: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38413: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38569: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38725: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot38881: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39037: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39193: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39349: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39505: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39661: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39817: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot39973: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot40129: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot40285: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
WARNING:param.BarPlot40441: Plotting hook <function plot_secondary_3_axes at 0x0000021870AB0670> could not be applied:

 list index out of range
Out[31]:
In [443]:
 
In [ ]: